using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace PMQLCHH.DAO
{
    public class KetNoiCSDLDAO
    {
        private static String _connectionString = "Data Source=.;Initial Catalog=PMQLCHH;Integrated Security=True";

       
        //Dung cho select bang
        public static DataTable ExecuteQuery(String sql)
        {
            DataTable dt = new DataTable();
            SqlConnection connect = new SqlConnection(_connectionString);
            connect.Open();
            SqlCommand command = connect.CreateCommand();
            command.CommandText = sql;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
            connect.Close();
            return dt;
        }

        //Dung cho Inser,Delete,Update
        public static bool ExecuteNonQuery(string sql)
        {
            bool kq;
            SqlConnection connect = new SqlConnection(_connectionString);
            connect.Open();
            SqlCommand command = connect.CreateCommand();
            command.CommandText = sql;
            int n = command.ExecuteNonQuery();
            if (n > 0)
            {
                kq = true;
            }
            else
            {
                kq = false;
            }
            connect.Close();
            return kq;
        }

        //truyen ten pro ko co tham so
        public static DataTable ExecuteQuery_Pro(string sql)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter dap = default(SqlDataAdapter);
            SqlConnection connect = new SqlConnection(_connectionString);
            connect.Open();
            SqlCommand sqlCommand = new SqlCommand(sql, connect);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            try
            {
                dap = new SqlDataAdapter(sqlCommand);
                dap.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                return dt;
            }
            finally
            {
                connect.Close();
            }
            return dt;
        }

        //Truyen ten pro co tham so
        public static DataTable ExecuteQuery_Pro_Param(string sql, SqlParameter[] @params)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter dap = default(SqlDataAdapter);
            SqlConnection connect = new SqlConnection(_connectionString);
            connect.Open();
            SqlCommand sqlCommand = new SqlCommand(sql, connect);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Clear();

            int n = @params.Length;
            if ((n > 0))
            {
                int i = 0;
                for (i = 0; i <= n - 1; i++)
                {
                    sqlCommand.Parameters.Add(@params[i]);
                }
            }
            try
            {
                dap = new SqlDataAdapter(sqlCommand);
                dap.Fill(ds, @params[0].Value.ToString());
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                return dt;
            }
            finally
            {
                connect.Close();
            }
            return dt;
        }

        //Dung cho cau pro insert,del,update
        public static bool ExecuteNonQuery_Pro(string sql, SqlParameter[] @params)
        {
            int n = @params.Length;
            SqlConnection connect = new SqlConnection(_connectionString);
            connect.Open();
            SqlCommand sqlCommand = new SqlCommand(sql, connect);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Clear();
            if ((n > 0))
            {
                int i = 0;
                for (i = 0; i <= n - 1; i++)
                {
                    sqlCommand.Parameters.Add(@params[i]);
                }
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                connect.Close();
            }
            return true;

        }
        
    }
}
